/* This script is create script which create tables, primary keys and uniques */
/* VV, 15/07/2003 */

create table #tmp(
	name		varchar(255), 
	id_obj		numeric(10), 
	id_part		numeric(10), 
	id_order	numeric(10), 
	txt			varchar(255)
)
go

/*insert "create table table_name(" for all tables*/
/*id_part = 1*/
insert into #tmp
select obj.name, obj.id, 1, 0, "create table " + obj.name + "(" + char(10) 
from sysobjects obj
where obj.type = 'U'
order by obj.name
go

/*insert column definition*/
/*id_part = 2*/
declare ccolumn cursor for	
select	sysobjects.id id,
		sysobjects.name tabname, 
		syscolumns.name colname,
		systypes.name coltype,
		syscolumns.length collength,
		syscolumns.status&8 as is_null,
		syscolumns.status&128 as is_identity,
		syscolumns.cdefault,
		syscomments.text defval,
		syscolumns.prec prec,
		syscolumns.scale scale,
		syscolumns.colid colid
from	sysobjects, syscolumns, systypes, syscomments
where	sysobjects.type = 'U' and  
		syscolumns.id = sysobjects.id and
		systypes.usertype = syscolumns.usertype and
		syscolumns.cdefault *= syscomments.id and
		upper(syscomments.text) like 'DEFAULT %'
order by tabname,syscolumns.colid
go

declare @txt varchar(255)
declare @tabname varchar(255), @colname varchar(255), @coltype varchar(255), @default varchar(255)
declare @id numeric, @collength numeric, @isnull numeric, @isidentity numeric, @prec numeric, @scale numeric, @colid numeric
declare @cdefault int

open ccolumn
fetch ccolumn into @id, @tabname, @colname, @coltype, @collength, @isnull, @isidentity, @cdefault, @default, @prec, @scale, @colid

while (@@sqlstatus = 0) begin
	if @colid != 1 select @txt = "," + char(10) + "	" else select @txt = "	"
	select @txt = @txt + @colname + " " + @coltype
	if (@coltype = 'varchar') or (@coltype = 'char') select @txt = @txt + "("+convert(varchar(10),@collength)+")"
	if (@coltype = 'numeric') select @txt = @txt + "("+convert(varchar(10),@prec)+","+convert(varchar(10),@scale)+")"
	if (@coltype = 'decimal') select @txt = @txt + "("+convert(varchar(10),@prec)+","+convert(varchar(10),@scale)+")"
	if @default! = null select @txt = @txt + " " + @default
	if @default is null and @cdefault>0 begin
		select @default=u.name+'.'+o.name from sysobjects o,sysusers u where o.id=@cdefault and o.uid=u.uid
		insert into #tmp  values (@tabname, @id, 6, @colid, "exec sp_bindefault '"+@default+"', '"+@tabname+'.'+@colname+"'"+char(10)+'go'+char(10) )
	end
	if @isidentity != 0 select @txt = @txt + " identity"
		else if @isnull != 0 select @txt = @txt + " null"
			else select @txt = @txt + " not null"
	--select @txt = @txt + char(10)
	insert into #tmp  values (@tabname, @id, 2, @colid, @txt)
	fetch ccolumn into @id, @tabname, @colname, @coltype, @collength, @isnull, @isidentity, @cdefault, @default, @prec, @scale, @colid
end

close ccolumn
deallocate cursor ccolumn
go

/*insert constraints*/
/*id_part = 3*/
declare		cindex cursor for
select 		id,
			name,
			keycnt,
			indid,
			status,
			status2
from   		sysindexes
where	  	indid > 0
and 		status2 & 2 = 2
order by 	id
go

declare @txt varchar(255)
declare @indname varchar(255),  @index varchar(255), @index0 varchar(255), @objname varchar(255)
declare @id numeric, @cnt numeric, @indid numeric, @status int, @status2 int, @i numeric
open cindex
fetch cindex into @id, @indname, @cnt, @indid, @status, @status2

while (@@sqlstatus = 0) begin
	select @txt = ","+ char(10)
	select @txt = @txt + "	constraint " + @indname
	if (@status & 2048 = 2048) select @txt = @txt + " primary key"
	else select @txt = @txt + " unique"

	select @index = '', @i = 1

	select @objname = name from sysobjects where id = @id
	/*Searchig columns*/
	while @i <= @cnt
	begin
		select @index0=index_col(@objname, @indid, @i)
		if @index0 is not null begin
			if @i > 1 select @index = @index + ", "
			select @index = @index + @index0
		end
		select @i = @i + 1
	end

	if @indid = 1 select @txt = @txt + " clustered"
	if @indid > 1
	begin
		if (@status2 & 512 = 512) select @txt = @txt + " clustered"
		else select @txt = @txt + " nonclustered"
	end
	select @txt =  @txt + " ("+@index+")"
	
	insert into #tmp  values (@objname, @id, 3, 0, @txt)
	fetch cindex into @id, @indname, @cnt, @indid, @status, @status2
end

close cindex
deallocate cursor cindex
go

/*Insert ")"*/
/*id_part = 4*/
insert into #tmp
select obj.name, obj.id, 4, 0, char(10) + ")" + char(10) + "go" + char(10) + char(10)
from sysobjects obj
where obj.type = 'U'
order by obj.name
go

/* Insert "exec sp_primarykey..." */
/*id_part = 5*/
declare cobject cursor for
select id, name from sysobjects where type = 'U'
go

declare @id numeric, @keycnt numeric, @colcolid numeric
declare @txt varchar(255), @objname varchar(255), @colname varchar(255)
open cobject
fetch cobject into @id, @objname
while (@@sqlstatus = 0) begin
	select	id = syscolumns.id,
			colname = syscolumns.name, 
			colcolid = syscolumns.colid
	into 	#pkey 
	from	syscolumns, syskeys
	where	syskeys.id = @id 
		and syscolumns.id = @id
		and syskeys.type = 1 
		and syscolumns.colid in (key1, key2, key3, key4, key5, key6, key7, key8)
	
	select @keycnt = @@rowcount 
	
	/*Insert primary key*/
	if @keycnt > 0 begin
		select @txt = "exec sp_primarykey '"+@objname+"', " 
		select @colcolid = 1
		while (@colcolid <= @keycnt) begin
			select	@colname = colname
			from 	#pkey
			where 	#pkey.colcolid = @colcolid
			if 	@colcolid > 1 select @txt = @txt + ", "
			select @txt = @txt + "'"+@colname+"'"
			select @colcolid = @colcolid + 1
		end
		select @txt = @txt + char(10)+"go"+char(10)+char(10)
		insert into #tmp  values (@objname, @id, 5, 0, @txt)
	end
	drop table #pkey
	fetch cobject into @id, @objname
end
close cobject
deallocate cursor cobject

/*Sorting*/
select txt from #tmp order by  name, id_obj, id_part, id_order

drop table #tmp
go
